﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data;

namespace eSilky.DAL
    {
    public class Category
        {

        #region Get Category List By Group Id
        /// <summary>
        /// Get Category List By GroupId
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public DataTable GetCategoryListByGroupId(int groupId)
        {
            OleDbConnection con = null;
            string qryString = "";
            OleDbDataAdapter da = null;
            DataSet ds = null;

            try
                {
                con = dBConnection.GetConnection();
                con.Open();
                qryString = "SELECT grp_id,cat_id,cat_name,cat_description,cat_status FROM " +
                    "esilky_category_master WHERE  cat_id = @groupId";
                da = new OleDbDataAdapter(qryString, con);
                da.SelectCommand.Parameters.Add(new OleDbParameter("@groupId", groupId));
                ds = new DataSet();
                da.Fill(ds);
                if (ds.Tables.Count > 0)
                    {
                    return (ds.Tables[0]);
                    }
                else
                    {
                    return new DataTable();
                    }
                }
            catch (Exception)
                {
                throw;
                }
            finally
                {
                con.Close();
                }
            }
        #endregion

        #region Save Category
        /// <summary>
        /// SaveCategory
        /// </summary>
        /// <param name="objCategory"></param>
        /// <returns></returns>
        public bool SaveCategory(VO.CategoryMaster objCategory)
            {
            OleDbConnection con = null;
            string qryString = "";
            OleDbCommand comObj = null;
            try
                {
                con = dBConnection.GetConnection();
                con.Open();
                qryString = "INSERT INTO esilky_category_master (grp_id,cat_name,cat_description,cat_status)" +
                    "VALUES (@groupId,@name,@description,@status)";
                comObj = new OleDbCommand(qryString, con);
                comObj.Parameters.Add(new OleDbParameter("@name", objCategory.name));
                comObj.Parameters.Add(new OleDbParameter("@description", objCategory.description));
                comObj.Parameters.Add(new OleDbParameter("@status", objCategory.status));
                comObj.Parameters.Add(new OleDbParameter("@groupId", objCategory.groupId));
                return Convert.ToBoolean(comObj.ExecuteNonQuery());
                }
            catch (Exception)
                {
                throw;
                }
            finally
                {
                con.Close();
                }
            }
        #endregion

        #region Update Category
        /// <summary>
        /// UpdateCategory
        /// </summary>
        /// <param name="objCategory"></param>
        /// <returns></returns>
        public bool UpdateCategory(VO.CategoryMaster objCategory)
            {
            OleDbConnection con = null;
            string qryString = "";
            OleDbCommand comObj = null;
            try
                {
                con = dBConnection.GetConnection();
                con.Open();
                qryString = "UPDATE esilky_category_master SET grp_id =@groupId ," +
                    "cat_name = @name,cat_description= @description,cat_status = @status WHERE cat_id = @catId";
                comObj = new OleDbCommand(qryString, con);
                comObj.Parameters.Add(new OleDbParameter("@name", objCategory.name));
                comObj.Parameters.Add(new OleDbParameter("@description", objCategory.description));
                comObj.Parameters.Add(new OleDbParameter("@status", objCategory.status));
                comObj.Parameters.Add(new OleDbParameter("@groupId", objCategory.groupId));
                comObj.Parameters.Add(new OleDbParameter("@catId", objCategory.id));
                return Convert.ToBoolean(comObj.ExecuteNonQuery());
                }
            catch (Exception)
                {
                throw;
                }
            finally
                {
                con.Close();
                }
            }
        #endregion

        #region Delete Category
        /// <summary>
        /// DELETE Category
        /// </summary>
        /// <param name="objCategory"></param>
        /// <returns></returns>
        public bool DeleteCategory(VO.CategoryMaster objCategory)
            {
            OleDbConnection con = null;
            string qryString = "";
            OleDbCommand comObj = null;
            try
                {
                con = dBConnection.GetConnection();
                con.Open();
                qryString = "DELETE FROM esilky_category_master WHERE cat_id = @catId"; 
                comObj = new OleDbCommand(qryString, con);
                comObj.Parameters.Add(new OleDbParameter("@catId", objCategory.id));
                return Convert.ToBoolean(comObj.ExecuteNonQuery());
                }
            catch (Exception)
                {
                throw;
                }
            finally
                {
                con.Close();
                }
            }
        #endregion

        }
    }